package com.kma.ncpractice2013.dao;

import com.kma.ncpractice2013.model.Order;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import javax.activation.DataSource;
import javax.sql.RowSet;

/**
 *
 * @author Illya
 */
public class OrderDAO extends DAO<Order>
{



    private static final String SQL_INSERT_QUERY = "INSERT INTO ORDERS(USER_ID,HOTEL_ID,ROOMS,ADULTS,CHILDREN,"
            + "NIGHTS,STARS,PRICE,DATE_OF_ORDER,CHECK_IN_DATE,CHECK_OUT_DATE, CONFIRMATION_TOKEN, BONUS_GAINED, AMENITY_MASK)" +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String SQL_UPDATE_QUERY = "UPDATE ORDERS SET USER_ID = ?,HOTEL_ID = ?,ROOMS = ?,ADULTS = ?,"
                + "CHILDREN = ?,NIGHTS = ?,STARS = ?,PRICE = ?,DATE_OF_ORDER = ?, "
                + "CHECK_IN_DATE = ?,CHECK_OUT_DATE = ?, CONFIRMATION_TOKEN = ?, BONUS_GAINED = ?, AMENITY_MASK = ? WHERE ID = ?";
    private static final String SQL_SELECT_BY_ID = "SELECT * FROM ORDERS WHERE ID = ?";
    private static final String SQL_SELECT_BY_TOKEN = "SELECT * FROM ORDERS WHERE CONFIRMATION_TOKEN = ?";

    public void create(Order order)
    {
        String sql = SQL_INSERT_QUERY;
        jdbcTemplate.update(sql, order.getUserId(),
                            order.getLocationId(),order.getRooms(),order.getAdults(),order.getChildren(),order.getNights(),
                            order.getHotelStars(),order.getPrice(),order.getOrderDate(),order.getCheckInDate(),
                            order.getCheckOutDate(), order.getConfirmationToken(), order.getBonusGained(), order.getAmenityMask());
    }

    public void update(Order order)
    {
        String sql = SQL_UPDATE_QUERY;
        jdbcTemplate.update(sql, order.getUserId(),order.getLocationId(),
                            order.getRooms(),order.getAdults(),order.getChildren(),order.getNights(),
                            order.getHotelStars(),order.getPrice(),order.getOrderDate(),order.getCheckInDate(),
                            order.getCheckOutDate(), order.getConfirmationToken(), order.getBonusGained(), order.getAmenityMask(), order.getId());
    }

    public Order getById(int id)
    {
        List<Order> orders;
        String sql = SQL_SELECT_BY_ID;
        orders = jdbcTemplate.query(sql, new OrderRowMapper(), id);
        return (orders == null) ? null : orders.get(0);
    }

    public Order getByConfirmationToken(String token)
    {
        List<Order> orders;
        String sql = SQL_SELECT_BY_TOKEN;
        orders = jdbcTemplate.query(sql, new OrderRowMapper(),token);
        return (orders == null) ? null : orders.get(0);
    }

    public List<Order> getAll()
    {
        String sql = "SELECT  * FROM ORDERS ";
        List<Order> orders = jdbcTemplate.query(sql, new OrderRowMapper());
        return orders;
    }
	public List<Order> getAllOrderedBy(String field, int count, int n)   //gets n-th page of results, page has "count" entries
	{

		String sql = "SELECT * FROM (SELECT * FROM ORDERS ORDER BY "+field+" DESC) WHERE (ROWNUM < ?*(?+1) AND ROWNUM >= ?*?)";
		return jdbcTemplate.query(sql, new OrderRowMapper(), count, n, count, n);
	}

	public List<Order> getAllOrderedBy(String field)
	{

		String sql = "SELECT * FROM ORDERS ORDER BY "+field+" DESC";
		return jdbcTemplate.query(sql, new OrderRowMapper());
	}

	public List<Order> getAllOrderedTimeframe(String field, Date start, Date end)
	{

		String sql = "SELECT * FROM ORDERS WHERE DATE_OF_ORDER > ? AND DATE_OF_ORDER < ?  ORDER BY "+field+" DESC";
		return jdbcTemplate.query(sql, new OrderRowMapper(), start, end);
	}

	public int getCount()
	{
		String sql = "SELECT COUNT(*) FROM ORDERS";

		SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql);
		return sqlRowSet.getInt(0);

	}
	public List<Order> getByUser(int userId)
	{
		String sql = "SELECT * FROM ORDERS WHERE USER_ID = ?";
		return jdbcTemplate.query(sql, new OrderRowMapper(), userId);
	}

    public void deleteById(int id)
    {
        String sql = "DELETE FROM ORDERS WHERE ID = ?";
        jdbcTemplate.update(sql,id);
    }

    private static final class OrderRowMapper implements ParameterizedRowMapper<Order>
    {
        @Override
        public Order mapRow(ResultSet rs, int rowNum) throws SQLException
        {
            Order order = new Order();
            order.setId(rs.getInt("id"));
            order.setAdults(rs.getInt("adults"));
            order.setCheckInDate(rs.getDate("check_in_date"));
            order.setCheckOutDate(rs.getDate("check_out_date"));
            order.setChildren(rs.getInt("children"));
            order.setHotelStars(rs.getDouble("stars"));
            order.setLocationId(rs.getInt("hotel_id"));
            order.setNights(rs.getInt("nights"));
            order.setOrderDate(rs.getDate("date_of_order"));
            order.setPrice(rs.getDouble("price"));
            order.setRooms(rs.getInt("rooms"));
            order.setUserId(rs.getInt("USER_ID"));
	        order.setConfirmationToken(rs.getString("confirmation_token"));
	        order.setConfirmed(rs.getInt("confirmed"));
	        order.setBonusGained(rs.getDouble("bonus_gained"));
	        order.setAmenityMask(rs.getInt("amenity_mask"));
            return order;
        }
    }
}
